import sqlite3
from math import ceil
from utils import log_info, log_error, log_exception
from configs import DB_PATH
from dao.base_dao import BaseDAO
from models.question import Question

class QuestionsBankDAO(BaseDAO[Question]):
    def __init__(self):
        self.table_name = 'questions'
        super().__init__(Question)
        self.columns = ['id', 'Q', 'A', 'B', 'C', 'D', 'T', 'type', 'LA', 'LB', 'LC', 'F']

    def _get_primary_key(self):
        """获取表的主键"""
        return 'id'

    def _get_table_name(self):
        """获取表名"""
        return self.table_name

    def get_total_records(self, search_query=None, filter_la=False, filter_lb=False, filter_lc=False):
        """获取符合条件的总记录数"""
        try:
            with self._get_connection() as conn:
                cursor = conn.cursor()
                count_query = f"SELECT COUNT(*) FROM {self.table_name}"
                count_params = []
                count_conditions = []

                # 添加搜索条件
                if search_query:
                    search_term = f"%{search_query}%"
                    search_conditions = ["Q LIKE ?", "A LIKE ?", "B LIKE ?", "C LIKE ?", "D LIKE ?", "J LIKE ?", "P LIKE ?", "I LIKE ?"]
                    count_conditions.append(f"({' OR '.join(search_conditions)})")
                    count_params.extend([search_term] * 8)

                # 添加分类筛选条件
                category_conditions = []
                if filter_la:
                    category_conditions.append("LA = 1")
                if filter_lb:
                    category_conditions.append("LB = 1")
                if filter_lc:
                    category_conditions.append("LC = 1")

                if category_conditions:
                    count_conditions.append(f"({' AND '.join(category_conditions)})")

                # 组合条件
                if count_conditions:
                    count_query += " WHERE " + " AND ".join(count_conditions)

                cursor.execute(count_query, tuple(count_params))
                return cursor.fetchone()[0]
        except Exception as e:
            log_exception(f"获取总记录数失败: {e}")
            return 0

    def get_page_data(self, page_num, page_size, search_query=None, filter_la=False, filter_lb=False, filter_lc=False):
        """获取指定页的数据"""
        try:
            offset = (page_num - 1) * page_size
            query = f"SELECT {', '.join(self.columns)} FROM {self.table_name}"
            params = []

            # 构建WHERE子句
            search_conditions = []
            if search_query:
                search_term = f"%{search_query}%"
                search_conditions = ["Q LIKE ?", "A LIKE ?", "B LIKE ?", "C LIKE ?", "D LIKE ?", "J LIKE ?", "P LIKE ?", "I LIKE ?"]
                params.extend([search_term] * 8)

            # 添加分类筛选条件
            category_conditions = []
            if filter_la:
                category_conditions.append("LA = 1")
            if filter_lb:
                category_conditions.append("LB = 1")
            if filter_lc:
                category_conditions.append("LC = 1")

            # 组合条件
            combined_conditions = []
            if search_conditions:
                combined_conditions.append(f"({' OR '.join(search_conditions)})")
            if category_conditions:
                combined_conditions.append(f"({' AND '.join(category_conditions)})")

            if combined_conditions:
                query += " WHERE " + " AND ".join(combined_conditions)

            # 添加分页
            query += " LIMIT ? OFFSET ?"
            params.extend([page_size, offset])
            params = tuple(params)

            with self._get_connection() as conn:
                conn.row_factory = sqlite3.Row
                cursor = conn.cursor()
                cursor.execute(query, params)
                rows = cursor.fetchall()

                # 转换为字典列表
                data = []
                for row in rows:
                    data.append(dict(row))

                return data
        except Exception as e:
            log_exception(f"获取分页数据失败: {e}")
            return []

    def update_question(self, question_id, updated_data):
        """更新题目数据"""
        try:
            with self.get_connection() as conn:
                cursor = conn.cursor()

                # 构建更新语句
                set_clause = ', '.join([f"{col} = ?" for col in updated_data.keys()])
                query = f"UPDATE {self.table_name} SET {set_clause} WHERE id = ?"

                # 准备参数
                params = list(updated_data.values()) + [question_id]

                # 执行更新
                cursor.execute(query, params)
                conn.commit()
                return True
        except Exception as e:
            log_exception(f"更新题目失败: {e}")
            return False

    def get_question_by_id(self, question_id):
        """根据ID获取题目"""
        try:
            query = f"SELECT {', '.join(self.columns)} FROM {self.table_name} WHERE id = ?"
            with self.get_connection() as conn:
                conn.row_factory = sqlite3.Row
                cursor = conn.cursor()
                cursor.execute(query, (question_id,))
                row = cursor.fetchone()
                return dict(row) if row else None
        except Exception as e:
            log_exception(f"根据ID获取题目失败: {e}")
            return None